package entity.connector;

import entity.bean.Movie;
import entity.bean.Show;
import entity.bean.User;
import java.sql.*;

/**
 *
 * @author 52571783
 */
public class ShowDownloader {

    private int movieID;
    private int noOfShows;
    private Show shows[];

    public ShowDownloader(Movie movie, User user) {
        try {
            Connection con = ConnectionProvider.getCon();
            PreparedStatement ps = con.prepareStatement(
                    "SELECT * FROM movie_show WHERE movie_id = ? AND DATEDIFF(minute,GETDATE(),movie_time) > 10 AND "
                    + "(? > 0 OR DAY(movie_time) = DAY(GETDATE())) ORDER BY movie_time",
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ps.setInt(1, movie.getId());
            ps.setInt(2, user.getRole());
            ResultSet rs = ps.executeQuery();
            // find the number of shows
            rs.last();
            noOfShows = rs.getRow();
            shows = new Show[noOfShows];
            // store data from result set to static data members
            rs.beforeFirst();
            String timeString;
            int year, month, day, hour, minute;
            for (int i = 0; rs.next() != false; i++) {
                shows[i] = new Show(movie.getId());
                shows[i].setTime(TimeConvertor.stringToCalendar(rs.getString("movie_time")));
                shows[i].setHouseID(rs.getInt("house_id"));
                shows[i].setPrice(rs.getInt("price"));
            }
            // close resources after use
            rs.close();
            ps.close();
        } catch (Exception e) {
        }
    }

    public ShowDownloader(Movie movie, boolean login) {
        try {
            Connection con = ConnectionProvider.getCon();
            PreparedStatement ps = con.prepareStatement(
                    "SELECT * FROM movie_show WHERE movie_id = ? AND DATEDIFF(minute,GETDATE(),movie_time) > 10 AND "
                    + "(? > 0 OR DAY(movie_time) = DAY(GETDATE())) ORDER BY movie_time",
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ps.setInt(1, movie.getId());
            ps.setBoolean(2, login);
            ResultSet rs = ps.executeQuery();
            // find the number of shows
            rs.last();
            noOfShows = rs.getRow();
            shows = new Show[noOfShows];
            // store data from result set to static data members
            rs.beforeFirst();
            String timeString;
            int year, month, day, hour, minute;
            for (int i = 0; rs.next() != false; i++) {
                shows[i] = new Show(movie.getId());
                shows[i].setMovieName(movie.getName());
                shows[i].setTime(TimeConvertor.stringToCalendar(rs.getString("movie_time")));
                shows[i].setHouseID(rs.getInt("house_id"));
                shows[i].setPrice(rs.getInt("price"));
            }
            // close resources after use
            rs.close();
            ps.close();
        } catch (Exception e) {
        }
    }

    public ShowDownloader(int houseID) {
        try {
            Connection con = ConnectionProvider.getCon();
            PreparedStatement ps = con.prepareStatement(
                    "SELECT * FROM movie_show WHERE house_id = ? ORDER BY movie_time",
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ps.setInt(1, houseID);
            ResultSet rs = ps.executeQuery();
            // find the number of shows
            rs.last();
            noOfShows = rs.getRow();
            shows = new Show[noOfShows];
            // store data from result set to static data members
            rs.beforeFirst();
            String timeString;
            int year, month, day, hour, minute;
            for (int i = 0; rs.next() != false; i++) {
                shows[i] = new Show(rs.getInt("movie_id"));
                shows[i].setTime(TimeConvertor.stringToCalendar(rs.getString("movie_time")));
                shows[i].setHouseID(rs.getInt("house_id"));
                shows[i].setPrice(rs.getInt("price"));
            }
            for (int i = 0; i < noOfShows; i++){
                // store movie name
                ps = con.prepareStatement(
                        "SELECT * FROM movie WHERE movie_id = ?",
                        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                ps.setInt(1, shows[i].getMovieID());
                rs = ps.executeQuery();
                rs.next();
                shows[i].setMovieName(rs.getString("movie_name"));
            }
            // close resources after use
            rs.close();
            ps.close();
        } catch (Exception e) {
        }
    }

    public int getNoOfShows() {
        return noOfShows;
    }

    public Show[] getShows() {
        return shows;
    }
    /*
     public static int noOfUnavailableSeats;
     public static int[] unavailableRowNo;
     public static int[] unavailableColNo;
     public static int noOfSoldSeats;
     public static int[] soldRowNo;
     public static int[] soldColNo;

     public static void updateHouse(Show show) throws SQLException {
     Connection con = ConnectionProvider.getCon();
     PreparedStatement ps = con.prepareStatement("SELECT house_no FROM movie_show WHERE movie_id = ? AND movie_time = ?");
     ps.setString(1, show.getId());
     ps.setString(2, show.getTime());
     ResultSet rs = ps.executeQuery();
     rs.next();
     show.setHouseNo(rs.getInt("house_no"));
     // close resources after use
     if (rs != null) {
     rs.close();
     }
     if (ps != null) {
     ps.close();
     }
     }

     public static void updateSeatingPlan(final Show SHOW) {
     try {
     Connection con = ConnectionProvider.getCon();
     // Update Unavailable Seats
     PreparedStatement ps = con.prepareStatement("SELECT * FROM unavailable_seat WHERE house_no = ? ORDER BY row_no, col_no", ResultSet.TYPE_SCROLL_INSENSITIVE,
     ResultSet.CONCUR_READ_ONLY);
     ps.setInt(1, SHOW.getHouseNo());
     ResultSet rs = ps.executeQuery();
     // find the number of seats
     while (rs != null && rs.next() != false) {
     noOfUnavailableSeats = rs.getRow();
     }
     unavailableRowNo = new int[noOfUnavailableSeats];
     unavailableColNo = new int[noOfUnavailableSeats];
     // store data from result set to static data members
     rs.beforeFirst();
     for (int i = 0; rs.next() != false; i++) {
     unavailableRowNo[i] = rs.getInt("row_no");
     unavailableColNo[i] = rs.getInt("col_no");
     }


     // Update Sold Seats
     ps = con.prepareStatement("SELECT * FROM movie_ticket WHERE movie_id = ? AND movie_time = ? ORDER BY row_no, col_no ASC", ResultSet.TYPE_SCROLL_INSENSITIVE,
     ResultSet.CONCUR_READ_ONLY);
     ps.setString(1, SHOW.getId());
     ps.setString(2, SHOW.getTime());
     rs = ps.executeQuery();
     // find the number of seats
     while (rs != null && rs.next() != false) {
     noOfSoldSeats = rs.getRow();
     }
     soldRowNo = new int[noOfSoldSeats];
     soldColNo = new int[noOfSoldSeats];
     // store data from result set to static data members
     rs.beforeFirst();
     for (int i = 0; rs.next() != false; i++) {
     soldRowNo[i] = rs.getInt("row_no");
     soldColNo[i] = rs.getInt("col_no");
     }

     // close resources after use
     if (rs != null) {
     rs.close();
     }
     if (ps != null) {
     ps.close();
     }
     } catch (Exception e) {
     }
     }

     public static boolean unavailableSeat(final Seat SEAT) {
     int rowNo = SEAT.getRowNo();
     int colNo = SEAT.getColNo();

     for (int i = 0; i < noOfUnavailableSeats && unavailableRowNo[i] <= rowNo; i++) {
     if (unavailableRowNo[i] == rowNo && unavailableColNo[i] == colNo) {
     return true;
     }
     }
     return false;
     }

     public static boolean soldSeat(final Seat SEAT) {
     int rowNo = SEAT.getRowNo();
     int colNo = SEAT.getColNo();
     for (int i = 0; i < noOfSoldSeats && soldRowNo[i] <= rowNo; i++) {
     if (soldRowNo[i] == rowNo && soldColNo[i] == colNo) {
     return true;
     }
     }
     return false;
     }*/
}
